package com.feib.stms.dao.jdbc;

import java.sql.Types;
import java.util.Date;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;


import com.feib.stms.dao.CodeTableDao;
import com.feib.stms.dao.jdbc.rowmapper.CodeTableRowMapper;
import com.feib.stms.model.CodeTable;
import com.feib.stms.util.UserUtil;


@Repository("codeTableDao")
public class CodeTableJdbcDao extends BaseJdbcDao implements CodeTableDao {
	
	protected OracleSequenceMaxValueIncrementer sequence;
	private SimpleJdbcInsert codeTableJdbcInsert;
	
	
	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "CODE_TABLE_PK_SEQ");

		this.codeTableJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.codeTableJdbcInsert.withTableName("CODE_TABLE")
		.usingColumns("CODE_TABLE_PK", "CODE_KIND", "CODE_VALUE" ,"CODE_NAME" ,"SORT_INDEX"  				 
				,"CREATED_BY" ,"CREATED_DATE" ,"UPDATED_BY" ,"UPDATED_DATE" );
	}

	
	public List<CodeTable> findByCodeKind(String codeKind) {
		
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("SELECT *");
		sql.append(" FROM CODE_TABLE WHERE CODE_KIND = :CODE_KIND");
		parameters.addValue("CODE_KIND",  codeKind, Types.VARCHAR);		
		
		return this.getSimpleJdbcTemplate().query(sql.toString(), new CodeTableRowMapper(), parameters);
	}

	public CodeTable getCodeTable(String codeKind, String codeValue) {
		
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("SELECT C.*");
		sql.append(" FROM  CODE_TABLE   C ");
		sql.append(" WHERE C.CODE_KIND =:CODE_KIND AND C.CODE_VALUE =:CODE_VALUE ");

		parameters.addValue("CODE_KIND",  codeKind, Types.VARCHAR);
		parameters.addValue("CODE_VALUE", codeValue, Types.VARCHAR);
		
		try{
			return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new CodeTableRowMapper(), parameters));
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	public int insert(CodeTable codeTable){
		
		int insertCounts = 0;
		
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		Long codeTablePk = new Long(this.sequence.nextLongValue());
			
		parameters.addValue("CODE_TABLE_PK", codeTablePk, Types.NUMERIC);
		parameters.addValue("CODE_KIND", codeTable.getCodeKind(), Types.VARCHAR);
		parameters.addValue("CODE_VALUE", codeTable.getCodeValue(), Types.VARCHAR);
		parameters.addValue("CODE_NAME", codeTable.getCodeName(), Types.VARCHAR);
		parameters.addValue("SORT_INDEX", codeTable.getSortIndex(), Types.NUMERIC);
		parameters.addValue("CREATED_DATE", codeTable.getCreatedDate(), Types.TIMESTAMP);
		parameters.addValue("CREATED_BY", codeTable.getCreatedBy(), Types.VARCHAR);
				
		insertCounts = codeTableJdbcInsert.execute(parameters);
		
		if (insertCounts > 0)
			codeTable.setCodeTablePk(codeTablePk);
		return insertCounts;
			
	}
	
	public int update(CodeTable codeTable){
		
		StringBuffer sql = new StringBuffer();
		String userId = UserUtil.getUserId();
		codeTable.setUpdatedBy(userId);
		codeTable.setUpdatedDate(new Date());
		
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("UPDATE CODE_TABLE SET CODE_KIND = :CODE_KIND,CODE_NAME = :CODE_NAME ,CODE_VALUE = :CODE_VALUE");
		sql.append(",SORT_INDEX = :SORT_INDEX");
		sql.append(",UPDATED_BY = :UPDATED_BY, UPDATED_DATE = :UPDATED_DATE ");
		sql.append(" WHERE CODE_TABLE_PK = :CODE_TABLE_PK");
		
		parameters.addValue("CODE_KIND", codeTable.getCodeKind(), Types.VARCHAR);
		parameters.addValue("CODE_NAME", codeTable.getCodeName(), Types.VARCHAR);
		parameters.addValue("CODE_VALUE", codeTable.getCodeValue(), Types.VARCHAR);
		parameters.addValue("SORT_INDEX", codeTable.getSortIndex(), Types.NUMERIC);
		parameters.addValue("UPDATED_BY",  codeTable.getUpdatedBy(), Types.VARCHAR);
		parameters.addValue("UPDATED_DATE",  codeTable.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("CODE_TABLE_PK", codeTable.getCodeTablePk(), Types.NUMERIC);
				
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}
}
